﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using IEMSOFT.EasyHotel.Common;
using IEMSOFT.EasyHotel.DAL.Models;
using IEMSOFT.EasyHotel.DAL.Models.Extension;
using IEMSOFT.Foundation;
namespace IEMSOFT.EasyHotel.DAL
{
    public class BillDAL
    {
        private  IDBProvider _dbProvider;
        public BillDAL(IDBProvider dbProvider)
        {
            _dbProvider = dbProvider;
        }

        public List<BillExtension> GetPendingBills(DateTime checkinDate,int subHotelId)
        {
            var sql = new StringBuilder();
            sql.Append(GetBasicQueryBillSql());
            sql.Append("where b.CheckinDate<=@0 and b.BillStatus=@1 and r.SubHotelId=@2");
            var ret = _dbProvider.DB.Fetch<BillExtension>(sql.ToString(),checkinDate, BillStatusType.Pending,subHotelId);
            return ret;
        }

        public BillExtension GetOnePendingBill(DateTime checkinDate, int roomId)
        {
            var sql = new StringBuilder();
            sql.Append(GetBasicQueryBillSql());
            sql.Append("where b.CheckinDate<=@0 and b.BillStatus=@1 and r.RoomId=@2");
            var ret = _dbProvider.DB.FirstOrDefault<BillExtension>(sql.ToString(), checkinDate, BillStatusType.Pending, roomId);
            return ret;
        }

        public void Cancel(long billId, int updatedUserId)
        {
            var sql = new StringBuilder();
            sql.Append("update Bill set BillStatus=@0,UpdatedByUserId=@1,Modified=now() ");
            sql.Append("where BillId=@2 ");
            _dbProvider.DB.Execute(sql.ToString(), BillStatusType.Cancel, updatedUserId, billId);
        }

        public Bill GetOneBill(long billId)
        {
            var sql = new StringBuilder();
            sql.Append("select * from  Bill b where b.BillId=@0");
            var ret = _dbProvider.DB.FirstOrDefault<Bill>(sql.ToString(), billId);
            return ret;
        }
        /// <summary>
        /// 
        /// </summary>
        /// <param name="fromDate">yyyy-MM-dd</param>
        /// <param name="endDate">yyyy-MM-dd</param>
        /// <param name="RoomTypeId"></param>
        /// <param name="TravelAgencyId"></param>
        /// <param name="PayTypeId"></param>
        /// <param name="SubHotelId"></param>
        /// <returns></returns>
        public List<BillExtension> SearchFinishedBills(string fromDate,
                                                       string endDate,
                                                       int? roomTypeId,
                                                       int? travelAgencyId,
                                                       int? payTypeId,
                                                       int? subHotelId,
                                                       int? createdByUserId,
                                                       int groupHotelId)
        {
            var sql=new StringBuilder();
            sql.Append(GetBasicQueryBillSql());
            sql.AppendFormat("where b.BillStatus={0} and sh.GroupHotelId={1} ", BillStatusType.Finished.ToInt(),groupHotelId);
            if (!string.IsNullOrEmpty(fromDate))
            {
                sql.AppendFormat("and b.CheckoutDate>='{0}' ", fromDate);
            }
            if (!string.IsNullOrEmpty(endDate))
            {
                sql.AppendFormat("and b.CheckoutDate<'{0}' ", endDate);
            }
            if (roomTypeId != null)
            {
                sql.AppendFormat("and rt.RoomTypeId={0} ", roomTypeId.Value);
            }
            if (travelAgencyId != null)
            {
                sql.AppendFormat("and b.TravelAgencyId={0} ", travelAgencyId.Value);
            }
            if (payTypeId != null)
            {
                sql.AppendFormat("and b.PayTypeId={0} ", payTypeId.Value);
            }

            if (subHotelId != null)
            {
                sql.AppendFormat("and r.SubHotelId={0} ", subHotelId.Value);
            }
            if (createdByUserId != null)
            {
                sql.AppendFormat("and b.CreatedByUserId={0} ", createdByUserId.Value);
            }
            sql.Append(" order by r.SubHotelId,b.CheckinDate asc ");
            var ret = _dbProvider.DB.Fetch<BillExtension>(sql.ToString());
            return ret;
        }


        private string GetBasicQueryBillSql()
        {
            var sql = new StringBuilder();
            sql.Append("select b.*, ");
            sql.Append("      ifnull(ta.Name,'散客') as TravelAgencyName,");
            sql.Append("      pt.Name as PayTypeName,");
            sql.Append("      u.FullName as CreateUserFullName, ");
            sql.Append("      u1.FullName as UpdateUserFullName, ");
            sql.Append("      r.RoomNo, ");
            sql.Append("      rt.Name as RoomTypeName, ");
            sql.Append("      r.RoomTypeId, ");
            sql.Append("      r.SubHotelId, ");
            sql.Append("      sh.Name as SubHotelName ");
            sql.Append("From Bill b ");
            sql.Append("inner join Room r on r.RoomId=b.RoomId ");
            sql.Append("inner join SubHotel  sh on sh.SubHotelId=r.SubHotelId ");
            sql.Append("left join RoomType rt on rt.RoomTypeId=r.RoomTypeId ");
            sql.Append("left join TravelAgency ta on b.TravelAgencyId=ta.TravelAgencyId ");
            sql.Append("left join PayType  pt on pt.PayTypeId=b.PayTypeId ");       
            sql.Append("left join User u on u.UserId=b.CreatedByUserId ");
            sql.Append("left join User u1 on u1.UserId=b.UpdatedByUserId ");
            return sql.ToString();
        }

        public void Settle(long billId, 
                          DateTime checkoutDate, 
                          int stayTotalDays,
                          decimal roomTotalFee,
                          decimal consumeFee,
                          string consumeItem,
                          string comments,
                          int stayTotalHours)
        {
            var sql = new StringBuilder();
            sql.Append("update Bill set  CheckoutDate=@0,StayTotalDays=@1,RoomTotalFee=@2,BillStatus=@3,ConsumeFee=@4");
            sql.Append(",consumeItem=@5,Comments=@6,StayTotalHours=@7,Modified=now() ");
            sql.Append("where BillId=@8 ");
            _dbProvider.DB.Execute(sql.ToString(), 
                                   checkoutDate, 
                                   stayTotalDays,
                                   roomTotalFee, 
                                   BillStatusType.Finished.ToInt(),
                                   consumeFee,
                                   consumeItem,
                                   comments,
                                   stayTotalHours,
                                   billId);
        }

        public Bill Create(Bill bill)
        {
          var ret=  _dbProvider.DB.Insert(bill);
          bill.BillId = Convert.ToInt64(ret);
          return bill;
        }

        public void Save(Bill bill)
        {
          _dbProvider.DB.Save(bill);
        }
    }
}
